﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GBMS.MODEL;
using KPIS.GERP.GBMS.DAL;
using System.Data.SqlClient;
//using MySql.Data;
//using System.Data.Odbc;
//using MySql.Data.MySqlClient;

namespace KPIS.GERP.GBMS.DAL
{
    public sealed class BgmKpiListDAL : IBgmKpiListDAL
    {
        ConvertNullable convert = new ConvertNullable();
        private IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        //private MySqlConnection conn = new MySqlConnection(SystemInfo._ConnectionString);

        public DataTable Search(int? intStrategyMapSeq, int? intKpiYear, string strKpiName, string strKpiUnit
            , string target_name, string record_status, string strType)
        {
            //string strSQL;
            //DataTable dtb = null;

            //try
            //{
            //    dbManager.Open();
            //    dbManager.CreateParameters(1);

            //    dbManager.AddParameters(0, "@status", SystemInfo._DeleteRecordStatus);

            //    strSQL = "SELECT * FROM bg_sp_kpi_header_sel WHERE record_status not like @status"
            //        + " and ;

            //    dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            //}
            //catch (Exception ex)
            //{
            //    throw ex;
            //}
            //finally
            //{
            //    dbManager.Dispose();
            //}
            //return dtb;

            string strSQL = "";
            //string strBgmKpiSeq = "";

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@intStrategyMapSeq", intStrategyMapSeq);
                dbManager.AddParameters(1, "@intKpiYear", intKpiYear);
                dbManager.AddParameters(2, "@vchrType", strType);
                dbManager.AddParameters(3, "@vharRecordStatus", SystemInfo._DeleteRecordStatus);
                //conn.Open();
                //sqlserverCommand cmd = new MySqlCommand();
                //cmd.Connection = conn;
                //cmd.CommandText = "bg_sp_kpi_header_sel";
                //cmd.CommandType = CommandType.StoredProcedure;
                //cmd.Parameters.AddWithValue("@intStrategyMapSeq", intStrategyMapSeq);
                //cmd.Parameters.AddWithValue("@intKpiYear", intKpiYear);
                //cmd.Parameters.AddWithValue("@vchrType", strType);
                //cmd.Parameters.AddWithValue("@vharRecordStatus", SystemInfo._DeleteRecordStatus);

                //MySqlDataAdapter da = new MySqlDataAdapter(cmd);

                strSQL = "bg_sp_kpi_header_sel @intStrategyMapSeq, @intKpiYear, @vchrType, @vharRecordStatus";

                DataTable dtb = dbManager.ExecuteDataTable(CommandType.Text, strSQL);
                //da.Fill(dtb);

                return dtb;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable LoadKpiTarget()
        {
             string strSQL;
             DataTable dtb = null;

             try
             {
                 dbManager.Open();
                 dbManager.CreateParameters(1);

                 dbManager.AddParameters(0, "@status", SystemInfo._DeleteRecordStatus);

                 strSQL = "SELECT target_seq, target_name FROM bgm_kpi_target WHERE record_status not like @status";

                 dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
             }
             catch (Exception ex)
             {
                 throw ex;
             }
             finally
             {
                 dbManager.Dispose();
             }
             return dtb;
        }

        public DataTable LoadKpiType()
        {
             string strSQL;
             DataTable dtb = null;

             try
             {
                 dbManager.Open();
                 dbManager.CreateParameters(1);

                 dbManager.AddParameters(0, "@status", SystemInfo._DeleteRecordStatus);
               
                 strSQL = @"SELECT bgm_kpi_type_seq,bgm_kpi_type_name
                            FROM bgm_mas_kpi_type
                            WHERE record_status not like @status ";

                 dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
             }
             catch (Exception ex)
            {
                throw ex;
            }
            finally
            {

                dbManager.Dispose();
            }
            return dtb;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="strStatMapping"></param>
        /// <param name="bgm_kpi_target_seq"></param>
        /// <param name="bgm_kpi_year"></param>
        /// <param name="created_by"></param>
        /// <param name="StrKpiName"></param>
        /// <param name="StrDes"></param>
        /// <param name="StrKpiType"></param>
        /// <param name="StrKpiUnit"></param>
        /// <param name="StrKpiWeight"></param>
        /// <param name="strType">1=Strategy 2=Project 3=????</param>
        /// <returns></returns>
        public string Insert(int intStatMapping, int intTargetSeq, int intKpiYear, int intCreatedBy
                           , string strKpiName, string strDes, string strKpiType, string strKpiUnit, int? intKpiWeight
                           , string strType)
        {
            //string strSQL = "";
            string strBgmKpiSeq  = "";
            SqlConnection SqlConn = new SqlConnection(SystemInfo._ConnectionString);

            try
            {
                
                //System.Data.SqlClient.SqlCommand sqlcomm = new System.Data.SqlClient.SqlCommand("bg_sp_kpi_header_ins", SqlConn);

                //SqlConn.Open();
                //sqlcomm.CommandType = CommandType.StoredProcedure;

                //sqlcomm.Parameters.AddWithValue("@intStrategyMapSeq", intStatMapping);
                //sqlcomm.Parameters.AddWithValue("@intTargetSeq", intTargetSeq);
                //sqlcomm.Parameters.AddWithValue("@intKpiYear", intKpiYear);
                //sqlcomm.Parameters.AddWithValue("@intCreatedBy", intCreatedBy);
                //sqlcomm.Parameters.AddWithValue("@vchrKpiName", strKpiName);
                //sqlcomm.Parameters.AddWithValue("@txtDescription", strDes);
                //sqlcomm.Parameters.AddWithValue("@intKpiTypeSeq", strKpiType);
                //sqlcomm.Parameters.AddWithValue("@vchrKpiUnit", strKpiUnit);
                //sqlcomm.Parameters.AddWithValue("@intWeight", convert.ConvertNullInt(intKpiWeight));
                //sqlcomm.Parameters.AddWithValue("@vchrType", strType);

                ////var returnParam = new SqlParameter
                ////{
                ////    ParameterName = "@intLastSeq",
                ////    DbType = System.Data.DbType.Int32,
                ////    Direction = ParameterDirection.ReturnValue
                ////};

                //var returnParam = sqlcomm.Parameters.AddWithValue("@intLastSeq", DbType.Int32);
                //returnParam.Direction = ParameterDirection.ReturnValue;
                //sqlcomm.ExecuteNonQuery(); // MISSING
                //strBgmKpiSeq = (string)returnParam.Value;


                dbManager.Open();
                dbManager.CreateParameters(11);
                dbManager.AddParameters(0, "@intStrategyMapSeq", intStatMapping);
                dbManager.AddParameters(1, "@intTargetSeq", intTargetSeq);
                dbManager.AddParameters(2, "@intKpiYear", intKpiYear);
                dbManager.AddParameters(3, "@intCreatedBy", intCreatedBy);
                dbManager.AddParameters(4, "@vchrKpiName", strKpiName);
                dbManager.AddParameters(5, "@txtDescription", strDes);
                dbManager.AddParameters(6, "@intKpiTypeSeq", strKpiType);
                dbManager.AddParameters(7, "@vchrKpiUnit", strKpiUnit);
                dbManager.AddParameters(8, "@intWeight", convert.ConvertNullInt(intKpiWeight));
                dbManager.AddParameters(9, "@vchrType", strType);
                dbManager.AddParameters(10, "@intLastSeq", strBgmKpiSeq);

                string strSQL = "exec bg_sp_kpi_header_ins @intStrategyMapSeq, @intTargetSeq, @intKpiYear, @intCreatedBy, @vchrKpiName"
                    + ", @txtDescription, @intKpiTypeSeq, @vchrKpiUnit, @intWeight, @vchrType, @intLastSeq";
                    //+ " select scope_identity();";
                //                    + " (strategy_map_seq,kpi_name,description,kpi_type_seq,target_seq,kpi_unit,weight,isweight,kpi_year] int NULL ,
                //[trouble] text COLLATE Thai_CI_AS NULL ,
                //[troubleshooting] text COLLATE Thai_CI_AS NULL ,
                //[remark] text COLLATE Thai_CI_AS NULL ,
                //[kpi_list_type_seq] int NOT NULL ,
                //[record_status] varchar(2) COLLATE Thai_CI_AS NULL ,
                //[created_by] int NOT NULL ,
                //[created_when)"
                //                    + "";
                //dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                strBgmKpiSeq = dbManager.ExecuteDataTable(CommandType.Text, strSQL).Rows[0][0].ToString();
                
                return strBgmKpiSeq;
            }
            catch (Exception ex)
            {
                //dbManager.RollBackTransaction();
                throw ex;
            }
            finally
            {
                SqlConn.Close();
            }
        }

        public void InsertTarget(string strBgmKpiSeq, string strStatMapping, Dictionary<int, double?> scores, string created_by)
        {
            string strSQL = String.Empty;
            var inti = strBgmKpiSeq;
            
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4 + (2*scores.Count));

                dbManager.AddParameters(0, "@strStratMapSeq", strStatMapping);
                dbManager.AddParameters(1, "@kpi_det_result", 0);
                dbManager.AddParameters(2, "@kpi_seq", strBgmKpiSeq);
                dbManager.AddParameters(3, "@created_by", created_by);

                int paramIndex = 4;
                int paramNameRunning = 1;
                foreach (KeyValuePair<int, double?> score in scores)
                {
                    string target_det_seq = "@target_det_seq" + paramNameRunning;
                    string kpi_det_target = "@kpi_det_target_" + paramNameRunning++;
                    dbManager.AddParameters(paramIndex++
                        , target_det_seq
                        , score.Key /* ไตรมาส 1 = 13 */);
                    dbManager.AddParameters(paramIndex++
                        , kpi_det_target
                        , (score.Value == 0 ? null : score.Value) /* ไตรมาส 1 คะแนน 43.33 */);


                    strSQL += "insert into bgm_kpi_detail" +
                             "(kpi_seq, target_det_seq, kpi_det_target, kpi_det_result, created_by)" +
                             "VALUES(@kpi_seq, " + target_det_seq + ", " + kpi_det_target + ", @kpi_det_result, @created_by);";
                
                }

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(string strKpiSeq, string StrKpiName, string StrDes, string StrKpiUnit, string strKpiType, 
                           string StrKpiWeight, string created_by, string strType )
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(8);

                dbManager.AddParameters(0, "@kpi_seq", strKpiSeq);
                dbManager.AddParameters(1, "@kpi_name", StrKpiName);
                dbManager.AddParameters(2, "@description", StrDes);
                dbManager.AddParameters(3, "@kpi_unit", StrKpiUnit);
                dbManager.AddParameters(4, "@weight", convert.ConvertNullInt(StrKpiWeight));
                dbManager.AddParameters(5, "@kpi_type_seq", strKpiType);
                dbManager.AddParameters(6, "@updated_by", created_by);
                dbManager.AddParameters(7, "@status", SystemInfo._DeleteRecordStatus);

                    strSQL = "UPDATE bgm_kpi_header SET"
                             + " kpi_name = @kpi_name,"
                             + " description = @description,"
                             + " kpi_unit = @kpi_unit,"
                             + " weight = @weight,"
                             + " kpi_type_seq = @kpi_type_seq,"
                             + " updated_by = @updated_by,"
                             + " updated_when = GetDate()"
                             + " WHERE kpi_seq = @kpi_seq AND record_status not like @status";
       
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }

            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(string strKpiSeq, string created_by)
        {
            string strSQL;

            try
            {

                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@kpi_seq", strKpiSeq);
                dbManager.AddParameters(1, "@updated_by", created_by);
                dbManager.AddParameters(2, "@status", SystemInfo._DeleteRecordStatus);
                
                strSQL = "UPDATE bgm_kpi_header "
                         +" SET record_status = @status,"
                         + " updated_by = @updated_by,"
                         + " updated_when = GetDate()"
                         + " WHERE kpi_seq = @kpi_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {   
                dbManager.Dispose();
            }
        }

        public List<MODEL.BGMKPI> LoadGridKpiTarget(string target_seq)
        {
            int yearStart = LoadYearType();
            List<MODEL.BGMKPI> list = new List<BGMKPI>();
            string strSQL;

            try
            {
               dbManager.Open();
               dbManager.CreateParameters(1);

               dbManager.AddParameters(0, "@target_seq", target_seq);

               strSQL = "select * FROM bgm_kpi_target_detail  WHERE  target_seq = @target_seq";
                    
               DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                int inti = 1;

                foreach (DataRow r in dtb.Rows)
                {
                    BGMKPI bgmkpi = new BGMKPI();
                    bgmkpi.TargetSeq = Convert.ToInt32(r["target_det_seq"]);
                    bgmkpi.Seq = Convert.ToInt32(r["target_det_seq"]);
                    bgmkpi.KpiTarget = Convert.ToString(r["target_name"]);
                    bgmkpi.Score = 0;
                    bgmkpi.Order = inti++;

                    list.Add(bgmkpi);
                }
                
                var queryAfter10 = from kpi in list where kpi.Order >= yearStart select kpi;
                var queryBefore10 = from kpi in list where kpi.Order < yearStart select kpi;
                var queryComplete = queryAfter10.Union(queryBefore10);
                list = queryComplete.ToList();

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return list;
        }

        public int LoadYearType()
        {
            int yearStart = 1;
            string strSQL;

            try
            {
                dbManager.Open();

                strSQL = "SELECT month_start FROM sys_year_type";

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                yearStart = Convert.ToInt32(dtb.Rows[0][0]);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
            return yearStart;
        }
        
        public bool CheckValue(string strKpiSeq)
        {
            string strSQL;

            bool result = true;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@kpi_seq", strKpiSeq);
                dbManager.AddParameters(1, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT kpi_seq, kpi_name, kpi_type_seq, kpi_unit,"
                          + " weight, kpi_year FROM bgm_kpi_header "
                          + " WHERE record_status = @status ";
                
                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    result = true;
                    break;
                }
            }
            catch (Exception error)
            {
                throw error;
            }
            finally 
            {
                dbManager.CloseReader();
                dbManager.Dispose();
             }
             
             return result;
        }

        public string CheckNameKpiTarget(int? intStrategyMapSeq, string strType)
        {
            string strSQL = "";
            string checkNameKpiTarget = "";

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@strStatMapSeq", intStrategyMapSeq);
                dbManager.AddParameters(1, "@status", SystemInfo._DeleteRecordStatus);

                if (strType == "2")
                {

                    strSQL = "SELECT CONCAT(klt.type_name, dl.bgm_strat_list_name) AS TargetName From bgm_strategy_data_mapping msdm"
                             + " LEFT JOIN bgm_strategy_data_list dl ON msdm.bgm_strat_list_seq = dl.bgm_strat_list_seq"
                             + " LEFT JOIN bgm_mas_strategy_type mst ON dl.bgm_strat_type_seq = mst.bgm_strat_type_seq"
                             + " LEFT JOIN bgm_kpi_list_type klt ON mst.bgm_strat_type_seq = klt.kpi_list_type_seq"
                             + " WHERE msdm.bgm_strat_data_map_seq = @strStatMapSeq  AND mst.record_status not like @status";
                }
                else if (strType == "rq")
                {
                    strSQL = "SELECT bgm_req_name FROM bgm_request WHERE bgm_req_seq = @strStatMapSeq";
                }
                else if (strType == "pj")
                {
                    strSQL = "SELECT bgm_proj_name FROM bgm_project WHERE bgm_proj_seq = @strStatMapSeq";
                }

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                checkNameKpiTarget = dtb.Rows.Count > 0 ? Convert.ToString(dtb.Rows[0][0]) : "";
                return checkNameKpiTarget;
            }
            catch (Exception er)
            {
                throw er;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable SearchTarget(string strKpiSeq)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0,"@kpi_seq", strKpiSeq);
                dbManager.AddParameters(1,"@status", SystemInfo._DeleteRecordStatus);

                strSQL = " SELECT bkd.kpi_det_seq, bkd.target_det_seq AS TargetSeq, kdl.target_name AS KpiTarget, bkd.kpi_det_target AS Score FROM bgm_kpi_detail bkd "
                          +" LEFT JOIN bgm_kpi_header bkh ON bkh.kpi_seq = bkd.kpi_seq"
                          +" LEFT JOIN bgm_kpi_target_detail kdl ON bkd.target_det_seq = kdl.target_det_seq"
                          +" WHERE bkh.kpi_seq = @kpi_seq  AND bkh.record_status not like @status";
                
                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dtb;
             }
             catch (Exception ex)
             {
                 throw ex;
             }
             finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateTarget(string strKpiDetSeq, double? dblKpiDetTarget, string created_by)
        {
            string strSQL = String.Empty;

            try
            {
                    dbManager.Open();
                    dbManager.CreateParameters(4);

                    dbManager.AddParameters(0, "@kpi_det_seq", strKpiDetSeq);
                    dbManager.AddParameters(1, "@updated_by", created_by);
                    dbManager.AddParameters(2, "@status", SystemInfo._DeleteRecordStatus);
                    dbManager.AddParameters(3, "@kpi_det_target", dblKpiDetTarget);
                     
                    strSQL = " UPDATE bgm_kpi_detail "
                             + " SET kpi_det_target = @kpi_det_target, "
                             + " updated_by = @updated_by, "
                             + " updated_when = GetDate() "
                             + " WHERE kpi_det_seq = @kpi_det_seq";

                    dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void DeleteTarget(string strKpiSeq, string created_by)
        {
            string strSQL;

            try
            {

                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@kpi_seq", strKpiSeq);
                dbManager.AddParameters(1, "@updated_by", created_by);
                dbManager.AddParameters(2, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = "UPDATE bgm_kpi_detail"
                         + " SET record_status = @status,"
                         + " updated_by = @updated_by,"
                         + " updated_when = GetDate()"
                         + " WHERE kpi_seq = @kpi_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

   }
}